/**
 * File: sqlite.js.
 * Author: Dawn
 * SQLite底层操作封装类
 * 手机等终端设备使用的SQLite工具类, 需要HBuilderX 打包时 添加SQLite模块
 */
import CommonUtil from "@/static/utils/common-util"

let DBManage = {
  dbName: 'JMHome', // 数据库名称
  dbPath: '_doc/JMHome.db', // 数据库地址,推荐以下划线为开头   _doc/xxx.db

  /**
   * 判断数据库是否打开
   */
  isOpen() {
    let _this = this;
    // 数据库打开了就返回 true,否则返回 false
    return plus.sqlite.isOpenDatabase({
      name: _this.dbName,  // 数据库名称
      path: _this.dbPath  // 数据库地址
    });
  },

  /**
   * 创建数据库 或 有该数据库就打开
   * @param success
   * @param fail
   */
  openDB(success, fail) {
    let _this = this;
    plus.sqlite.openDatabase({
      name: _this.dbName,
      path: _this.dbPath,
      success: function(e){
        if(CommonUtil.isFunction(success)){
          success(e)
        }else {
          console.log("openDB success")
        }
      },
      fail: function(e){
        if(CommonUtil.isFunction(fail)){
          fail(e)
        }else {
          console.log("openDB error", e)
        }
      }
    });
  },

  /**
   * 关闭数据库
   * @param success
   * @param fail
   */
  closeDB(success, fail) {
    let _this = this;
    plus.sqlite.closeDatabase({
      name: _this.dbName,
      success: function(e){
        if(CommonUtil.isFunction(success)){
          success(e)
        }else {
          console.log("closeDB success")
        }
      },
      fail: function(e){
        if(CommonUtil.isFunction(fail)){
          fail(e)
        }else {
          console.log("closeDB error", e)
        }
      }
    })
  },

  /**
   * 最底层逻辑--执行sql
   * @param sql
   * @param success
   * @param fail
   */
  executeBySql(sql, success, fail) {
    // 非App端直接跳过
    if(!window.plus){
      success(null);
      return;
    }
    let _this = this;
    this.openDB(function (data) {
      plus.sqlite.executeSql({
        name: _this.dbName,
        sql: sql,
        success: function(e){
          if(CommonUtil.isFunction(success)){
            success(e)
          }else {
            console.log("executeBySql success", e)
          }
        },
        fail: function(e){
          if(CommonUtil.isFunction(fail)){
            fail(e)
          }else {
            console.log("executeBySql error", sql, e)
          }
        }
      })
      _this.closeDB()
    })
  },

  /**
   * 最底层逻辑--通过SQL执行查询
   *
   * @param tableName
   * @param sql
   * @param success
   * @param fail
   */
  selectBySql(sql, success, fail) {
    // 非App端直接跳过
    if(!window.plus){
      success(null);
      return;
    }
    let _this = this;
    this.openDB(function (data) {
      plus.sqlite.selectSql({
        name: _this.dbName,
        sql: sql,
        success: function(e){
          if(CommonUtil.isFunction(success)){
            success(e)
          }else {
            console.log("selectBySql success", e)
          }
        },
        fail: function(e){
          if(CommonUtil.isFunction(fail)){
            fail(e)
          }else {
            console.log("selectBySql error", sql, e)
          }
        }
      })
      _this.closeDB()
    })
  },

  /**
   * 数据库删表 sql:'DROP TABLE tableName'
   * @param tableName 表名
   * @param success
   * @param fail
   */
  dropTable(tableName, success, fail) {
    var sql = `DROP TABLE IF EXISTS ${tableName}`;
    this.executeBySql(sql, success, fail);
  },

  /**
   * 向表格里添加数据
   * sql:'INSERT INTO tableName VALUES('x','x','x')'                 对应新增
   * 或者
   * sql:'INSERT INTO tableName ('x','x','x') VALUES('x','x','x')'   具体新增
   *
   * @param tableName 表名
   * @param data 待添加数据
   * @param condition 条件
   * @param success
   * @param fail
   */
  insertTableData(tableName, data, condition, success, fail) {
    if (CommonUtil.isNotEmpty(condition)) {
      var sql = `INSERT INTO ${tableName} VALUES(${data})`;
    } else {
      var sql = `INSERT INTO ${tableName} (${condition}) VALUES(${data})`;
    }
    this.executeBySql(sql, success, fail);

  },

  /**
   * 根据条件向表格里添加数据  有数据更新、无数据插入
   *
   * (建表时需要设置主键) 例如 --- "roomid" varchar(50) PRIMARY KEY
   *
   * @param tableName
   * @param data
   * @param condition
   * @param success
   * @param fail
   */
  insertOrReplaceData(tableName, data, condition, success, fail) {
    if (CommonUtil.isEmpty(condition)) {
      var sql = `INSERT OR REPLACE INTO ${tableName} VALUES(${data})`;
    } else {
      var sql = `INSERT OR REPLACE INTO ${tableName} (${condition}) VALUES(${data})`;
    }
    this.executeBySql(sql, success, fail);
  },

  /**
   * 查询获取数据库里的数据
   *
   * @param tableName
   * @param whereCol0
   * @param whereVal0
   * @param success
   * @param fail
   */
  selectTableData(tableName, whereCol0, whereVal0, success, fail) {
    if (CommonUtil.isNotEmpty(whereCol0)) {
      // 一个检索条件
      var sql = `SELECT * FROM ${tableName} WHERE ${whereCol0} = '${whereVal0}'`;
    }
    if (CommonUtil.isEmpty(whereCol0)) {
      var sql = `SELECT * FROM ${tableName}`;
    }
    this.selectBySql(sql, success, fail);

  },

  /**
   * 通用条件查询
   * @param tableName 表名
   * @param where where条件
   * @param cols 查询字段
   * @param orderBy 排序字段
   * @param success
   * @param fail
   */
  selectByOps(tableName, where, cols, orderBy, success, fail) {
    var sql = "";
    if(CommonUtil.isEmpty(cols)) {
      sql = 'SELECT * FROM ' + tableName;
    }else {
      sql = 'SELECT ' + cols + ' FROM ' + tableName;
    }
    if(CommonUtil.isNotEmpty(where)) {
      sql += ' WHERE ' + where;
    }
    if(CommonUtil.isNotEmpty(orderBy)) {
      sql += ' ORDER BY ' + orderBy;
    }
    this.selectBySql(sql, success, fail);
  },

  /**
   * 删除表里的数据
   * @param tableName
   * @param where where条件
   * @param success
   * @param fail
   */
  deleteTableData(tableName, where, success, fail) {
    if (CommonUtil.isNotEmpty(where)) {
      var sql = `DELETE FROM ${tableName} WHERE ${where}`;
    } else {
      var sql = `DELETE FROM ${tableName}`;
    }
    this.executeBySql(sql, success, fail);
  },

  /**
   * 修改数据表里的数据
   *
   * @param tableName
   * @param cols
   * @param where
   * @param success
   * @param fail
   */
  updateTableData(tableName, cols, where, success, fail) {
    if (CommonUtil.isEmpty(where)) {
      var sql = `UPDATE ${tableName} SET ${cols}`;
    } else {
      var sql = `UPDATE ${tableName} SET ${cols} WHERE ${where}`;
    }
    this.executeBySql(sql, success, fail);
  },

  /**
   * @param tableName 表名
   * @param limit 单次拉取数量
   * @param offset 拉取偏移量
   * @param orderBy 排序条件
   * @param where 筛选条件
   * @param success
   * @param fail
   */
  selectForPage(tableName, limit, offset, orderBy, where, success, fail) {
    if(CommonUtil.isNotEmpty(orderBy) && CommonUtil.isNotEmpty(where)) {
      var sql = `SELECT * FROM ${tableName} where ${where} ORDER BY ${orderBy} LIMIT ${limit} OFFSET ${offset}`
    }else if(CommonUtil.isEmpty(orderBy) && CommonUtil.isNotEmpty(where)){
      var sql = `SELECT * FROM ${tableName} where ${where} LIMIT ${limit} OFFSET ${offset}`
    }else if(CommonUtil.isNotEmpty(orderBy) && CommonUtil.isEmpty(where)){
      var sql = `SELECT * FROM ${tableName} ORDER BY ${orderBy} LIMIT ${limit} OFFSET ${offset}`
    }else {
      var sql = `SELECT * FROM ${tableName} LIMIT ${limit} OFFSET ${offset}`
    }
    this.selectBySql(sql, success, fail);
  }
}
export default DBManage;